1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmAttendanceEntryRecord1
4
5 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
6 Me.Close()
7 End Sub
8 Sub Reset()
9 DateFrom.Text = Today
10 DateTo.Text = Now
11 lblWorkingDays.Visible = False
12 End Sub
13 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
14 Reset()
15 End Sub
16
17
18 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
19 Me.Close()
20 End Sub
21
22 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
23 Dim rowsTotal, colsTotal As Short
24 Dim I, j, iC As Short
25 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
26 Dim xlApp As New Excel.Application
27 Try
28 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
29 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
30 xlApp.Visible = True
31
32 rowsTotal = dgw.RowCount
33 colsTotal = dgw.Columns.Count - 1
34 With excelWorksheet
35 .Cells.Select()
36 .Cells.Delete()
37 For iC = 0 To colsTotal
38 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
39 Next
40 For I = 0 To rowsTotal - 1
41 For j = 0 To colsTotal
42 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
43 Next j
44 Next I
45 .Rows("1:1").Font.FontStyle = "Bold"
46 .Rows("1:1").Font.Size = 12
47
48 .Cells.Columns.AutoFit()
49 .Cells.Select()
50 .Cells.EntireColumn.AutoFit()
51 .Cells(1, 1).Select()
52 End With
53 Catch ex As Exception
54 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
55 Finally
56 'RELEASE ALLOACTED RESOURCES
57 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
58 xlApp = Nothing
59 End Try
60 End Sub
61
62 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
63 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
64 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
65 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
66 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
67 End If
68 Dim b As Brush = SystemBrushes.ControlText
69 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
70
71 End Sub
72
73 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
74 Try
75 lblWorkingDays.Visible = True
76 con = New SqlConnection(cs)
77 con.Open()
78 Dim sql As String = "select RTRIM(Staff.StaffID),RTRIM(StaffName),RTRIM(Designation),Count(StaffAttendance.Status) from StaffAttendance,Staff where Staff.St_ID=StaffAttendance.StaffID and WorkingDate between @d1 and @d2 and StaffAttendance.Status='P' group by Staff.StaffID,StaffName,designation order by StaffName"
79 cmd = New SqlCommand(sql, con)
80 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateIN").Value = DateFrom.Value.Date
81 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateIN").Value = DateTo.Value
82 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
83 dgw.Rows.Clear()
84 While (rdr.Read() = True)
85 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3))
86 End While
87 con.Close()
88 con = New SqlConnection(cs)
89 con.Open()
90 Dim sql1 As String = "select Count(StaffAttendance.Status) from StaffAttendance,Staff where Staff.St_ID=StaffAttendance.StaffID and WorkingDate between @d1 and @d2"
91 cmd = New SqlCommand(sql1, con)
92 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateIN").Value = DateFrom.Value.Date
93 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateIN").Value = DateTo.Value
94 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
95 If (rdr.Read() = True) Then
96 lblWorkingDays.Text = rdr.GetValue(0)
97 End If
98 con.Close()
99 Catch ex As Exception
100 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
101 End Try
102 End Sub
103 End Class
